import os
import subprocess
import datetime
import time

# 配置数据库连接信息, 下面是要备份的数据库，会保存在本地C:\backups
DB_CONFIG = {
    'host': '10.114.183.17',
    'port': '3306',
    'user': 'labuser',
    'password': '123456',  
    'databases': ['exceltodb', 'tcclabrecord20220523','django2']  # 支持多个数据库
}

# 配置远程数据库信息（目标服务器）
REMOTE_DB_CONFIG = {
    'host': '10.114.183.194',
    'port': '3306',
    'user': 'labuser',        # 远程数据库用户名
    'password': '123456'      # 远程数据库密码
}

# 配置备份文件存储路径
BACKUP_DIR = os.path.join('C:\\', 'backups')
# 配置保留天数
DAYS_TO_KEEP = 7

# 配置备份时间（24小时制）
BACKUP_TIME = "23:00"

def backup_database():
    """执行所有数据库的备份并同步到远程服务器"""
    # 创建备份目录
    os.makedirs(BACKUP_DIR, exist_ok=True)
    
    timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
    all_success = True
    
    # 遍历所有数据库进行备份
    for database in DB_CONFIG['databases']:
        # 生成备份文件名
        backup_file = os.path.join(BACKUP_DIR, f"{database}_{timestamp}.sql")
        
        # 构建并执行备份命令
        backup_cmd = [
            'mysqldump',
            f"--host={DB_CONFIG['host']}",
            f"--port={DB_CONFIG['port']}",
            f"--user={DB_CONFIG['user']}",
            f"--password={DB_CONFIG['password']}",
            f"--default-character-set=utf8mb4",  # 指定UTF-8编码
            database,
            f"--result-file={backup_file}"
        ]
        
        try:
            # 执行备份
            subprocess.run(backup_cmd, check=True)
            print(f"数据库 {database} 备份成功: {backup_file}")
            
            # 备份成功后，同步到远程数据库
            sync_success = sync_to_remote(database, backup_file)
            if not sync_success:
                all_success = False
                
        except subprocess.CalledProcessError as e:
            print(f"数据库 {database} 备份失败: {e}")
            all_success = False
    
    return all_success

def sync_to_remote(database, backup_file):
    """将备份文件同步到远程数据库"""
    try:
        # 构建导入命令，将本地SQL文件导入到远程数据库
        # 使用mysql命令并通过管道输入文件内容
        mysql_cmd = [
            'mysql',
            f"--host={REMOTE_DB_CONFIG['host']}",
            f"--port={REMOTE_DB_CONFIG['port']}",
            f"--user={REMOTE_DB_CONFIG['user']}",
            f"--password={REMOTE_DB_CONFIG['password']}",
            f"--default-character-set=utf8mb4",
            database
        ]
        
        # 读取备份文件并通过管道传递给mysql命令
        with open(backup_file, 'r', encoding='utf-8') as f:
            subprocess.run(
                mysql_cmd,
                stdin=f,
                check=True,
                capture_output=True,
                text=True
            )
        
        print(f"数据库 {database} 已成功同步到远程服务器 {REMOTE_DB_CONFIG['host']}")
        return True
        
    except subprocess.CalledProcessError as e:
        print(f"数据库 {database} 同步到远程服务器失败: {e.stderr}")
        return False
    except Exception as e:
        print(f"同步数据库 {database} 时发生错误: {str(e)}")
        return False

def cleanup_old_backups():
    """清理旧的备份文件"""
    current_time = datetime.datetime.now()
    
    for filename in os.listdir(BACKUP_DIR):
        if filename.endswith('.sql'):
            try:
                # 从文件名解析时间戳
                timestamp_str = filename.split('_', 1)[1].replace('.sql', '')
                file_time = datetime.datetime.strptime(timestamp_str, "%Y%m%d_%H%M%S")
                
                # 计算文件年龄
                age_days = (current_time - file_time).days
                
                # 删除超过保留期限的文件
                if age_days > DAYS_TO_KEEP:
                    file_path = os.path.join(BACKUP_DIR, filename)
                    os.remove(file_path)
                    print(f"已删除旧备份: {file_path}")
            except (ValueError, IndexError):
                # 跳过无法解析的文件
                continue

def calculate_next_backup_time(target_time_str):
    """精确计算下一次备份时间，避免漂移"""
    hour, minute = map(int, target_time_str.split(':'))
    now = datetime.datetime.now()
    
    # 设置目标时间
    target_time = now.replace(
        hour=hour, 
        minute=minute, 
        second=0, 
        microsecond=0
    )
    
    # 如果目标时间已过，设置为明天
    if now >= target_time:
        target_time += datetime.timedelta(days=1)
        
    return target_time

def wait_for_backup_time(target_time):
    """等待到指定时间执行备份"""
    while True:
        # 每次循环重新计算目标时间
        next_backup = calculate_next_backup_time(target_time)
        print(f"下一次备份将在 {next_backup.strftime('%Y-%m-%d %H:%M:%S')} 执行")
        
        # 计算精确等待时间
        wait_seconds = (next_backup - datetime.datetime.now()).total_seconds()
        if wait_seconds > 0:
            time.sleep(wait_seconds)
            return

if __name__ == "__main__":
    print(f"MySQL自动备份与同步脚本已启动，每天{BACKUP_TIME}执行备份")
    print(f"备份源: {DB_CONFIG['host']}，同步目标: {REMOTE_DB_CONFIG['host']}")
    
    # 启动时立即执行一次备份
    success = backup_database()
    if success:
        cleanup_old_backups()
    
    while True:
        # 等待到备份时间
        wait_for_backup_time(BACKUP_TIME)
        
        # 执行备份
        success = backup_database()
        
        # 清理旧备份
        if success:
            cleanup_old_backups()
